package com.taocore.top.title.service;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URI;
import java.net.URISyntaxException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.naming.NamingException;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import net.sf.json.JSONArray;
import net.sf.json.JSONException;
import net.sf.json.JSONObject;

import org.apache.commons.io.IOUtils;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.utils.URIBuilder;
import org.apache.http.impl.client.DefaultHttpClient;

import com.taocore.top.title.db.DBUtils;
import com.taocore.top.title.db.Dao;
import com.taocore.top.title.domain.WeeklyKeyword;

public class WeeklyKeywordsService {
	static Logger _log = Logger.getLogger(WeeklyKeywordsService.class.getName());
	static Pattern CLICKS_PATTERN = Pattern.compile("(\\d+).*");

	public static String importData(String path) throws BiffException, IOException
	{
		Workbook workbook = Workbook.getWorkbook(new File(path));
		Sheet sheet = workbook.getSheet(0);
		Connection conn = null;
		PreparedStatement statement = null;
		try {
			conn = DBUtils.getConnection();
			Dao.INSTANCE.clearWeeklyKeywords(conn);
			String sql = "insert into week_keywords_t(c1_name_c, c2_name_c, c3_name_c, keyword_c, clicks_c) values(?,?,?,?,?)";
			statement = conn.prepareStatement(sql);
			for (int i = 1, n = sheet.getRows(); i < n; i++)
			{
				String c1 = sheet.getCell(0, i).getContents();
				String c2 = sheet.getCell(1, i).getContents();
				String c3 = sheet.getCell(2, i).getContents();
				String keyword = sheet.getCell(3, i).getContents();
				String clicksStr = sheet.getCell(5, i).getContents();
				long clicks = extractClicks(clicksStr);
				statement.setString(1, c1);
				statement.setString(2, c2);
				statement.setString(3, c3);
				statement.setString(4, keyword);
				statement.setLong(5, clicks);
				statement.addBatch();
			}
			statement.executeBatch();
		} 
		catch (NamingException e) {
			_log.log(Level.SEVERE, "", e);
		}
		catch (SQLException e) {
			_log.log(Level.SEVERE, "", e);
		}
		finally{
			DBUtils.close(conn, statement, null);
		}
		return null;
	}
	
	private static long extractClicks(String clicksStr)
	{
		Matcher m = CLICKS_PATTERN.matcher(clicksStr);
		if(m.find())
		{
			clicksStr = m.group(1);
		}
		return Long.parseLong(clicksStr);
	}
	
	public static List<WeeklyKeyword> filterWeeklyKeywords(String keyword) throws URISyntaxException, ClientProtocolException, IOException
	{
		HttpClient httpclient = new DefaultHttpClient();
		URIBuilder builder = new URIBuilder();
		builder.setScheme("http").setHost("suggest.taobao.com").setPath("/sug")
		    .setParameter("extras", "1")
		    .setParameter("code", "utf-8")
		    .setParameter("q", keyword);
		URI uri = builder.build();
		_log.info("uri: " + uri);
		HttpGet get = new HttpGet(uri);
		HttpResponse response = httpclient.execute(get);
		HttpEntity entity = response.getEntity();
		if (entity != null) {
		    InputStream in = entity.getContent();
		    try {
		        String json = IOUtils.toString(in, "UTF-8");
		        _log.info(json);
		        JSONObject jsonObject = JSONObject.fromObject(json);
		        JSONArray array = jsonObject.getJSONArray("result");
		        _log.info("array.size: " + array.size());
		        List<WeeklyKeyword> words = new ArrayList<WeeklyKeyword>();
		        for (int i = 0, n = array.size(); i < n; i++)
		        {
		        	JSONArray keywordArray = array.getJSONArray(i);
		        	String word = keywordArray.getString(0);
		        	WeeklyKeyword w = new WeeklyKeyword();
		        	w.setKeyword(word);
		        	words.add(w);
		        }
		        return words;
		    }
		    catch (JSONException e)
		    {
		    	_log.log(Level.SEVERE, "", e);
		    }
		    catch (Exception e)
		    {
		    	_log.log(Level.SEVERE, "", e);
		    }
		    finally {
		        in.close();
		    }
		}
		return null;
	}
}
